/*****************************************************************************
 * HUSONG PROPRIETARY INFORMATION
 * <p/>
 * The information contained herein is proprietary to HUSONG
 * and shall not be reproduced or disclosed in whole or in part
 * or used for any design or manufacture
 * without direct written authorization from HUSONG.
 * <p/>
 * Copyright (c) 2015 by HUSONG.  All rights reserved.
 *****************************************************************************/
package com.husong.mapper;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.UpdateProvider;
import org.springframework.stereotype.Component;

import com.husong.entity.BatchWithdrawOrder;
import com.husong.entity.Feedback;
import com.husong.entity.Institution;
import com.husong.entity.Invite;
import com.husong.entity.PayOrder;
import com.husong.entity.RechargeList;
import com.husong.entity.TrainPic;
import com.husong.entity.TrainTest;
import com.husong.entity.User;
import com.husong.entity.WithdrawOrder;
import com.husong.mapper.provider.UserProvider;
import com.husong.pojo.AccountBindBean;
import com.husong.pojo.BillBean;
import com.husong.pojo.PayOrderBean;
import com.husong.pojo.UserModel;
import com.husong.pojo.UserWalletBean;
import com.husong.pojo.UserWalletLogBean;
import com.husong.pojo.WalletBean;
import com.husong.pojo.WithdrawOrderBean;

/**
 * @author maxmin
 */
@Component
public interface UserMapper {

    /**
     * 根据id查询user
     *
     * @param id
     *
     * @return
     */
	@Select(value = "SELECT ifnull((select SUM(e.point)/COUNT(e.point) from hs_evaluate e where e.userid=u.id),0) as stars,u.id as userId,u.username,u.user_status status,u.header,u.telephone,u.notifyid,"
            + "u.level,u.istested,u.istrained,u.isauthen,u.cityname as cityName,u.point,u.gender,u.isbroker isbroker,u.create_date as createDate,u.update_date as updateDate,u.birthday"
            + ",b.status as brokerStatus,u.declaration,b.realname as realName,b.authen_tel as authenTelephone,b.id_num as idNum,b.tag"
            + " FROM hs_user u "
            + " left join hs_broker b on b.user_id=u.id WHERE id=#{id} AND isdelete=0 ")
    User getUserById(String id);

    /**
     * 查询所有经纪人数量
     *
     * @return
     */
    @Select(value = "SELECT COUNT(1) FROM hs_user WHERE isbroker=1 AND isdelete=0 AND cityname=#{cityname}")
    Long brokerCount(String cityname);

    /**
     * 新生成用户
     *
     * @param user 用户对象
     *
     * @return 是否成功
     */
    @Insert(value = "INSERT INTO hs_user (id,telephone,cityname,notifyid,username,create_date,header,birthday) " +
            "values(#{userId},#{telephone},#{cityName},#{notifyid},#{username},#{createDate},#{header},#{birthday})")
    int addUser(User user);

    /**
     * 使用手机号码查找用户
     *
     * @param telephone 手机号
     *
     * @return 用户实体
     */
    @Select(value = "SELECT ifnull((select SUM(e.point)/COUNT(e.point) from hs_evaluate e where e.userid=u.id),0) as stars,u.id as userId,u.username,u.user_status status,u.header,u.telephone,u.notifyid,"
            + "u.level,u.istested,u.istrained,u.isauthen,u.cityname as cityName,u.point,u.gender,u.isbroker isbroker,u.create_date as createDate,u.update_date as updateDate,u.birthday"
            + ",b.status as brokerStatus,u.declaration,b.realname as realName,b.authen_tel as authenTelephone,b.id_num as idNum,b.tag"
            + " FROM hs_user u "
            + " left join hs_broker b on b.user_id=u.id WHERE u.telephone=#{telephone} AND u.isdelete=0 ")
    User getUserByTelephone(String telephone);
    
    /**
	 * 培训内容的图片
	 * @return
	 */
	@Select(value = "SELECT tp.trainpicid,tp.pic_path,tp.`index`,tp.createtime,tp.updatetime FROM hs_train_pic tp order by tp.`index`")
	@Results({
		@Result(column="trainpicid",property="trainPicId"),
		@Result(column="pic_path",property="picPath")
	})
	public abstract List<TrainPic> trainPics();
	
	/**
	 * 培训考试题目列表
	 * @return
	 */
	@Select(value= "SELECT tt.testid,tt.answer,tt.correct,tt.createtime,tt.updatetime,tt.`index` FROM hs_train_test tt order by tt.`index`")
	public abstract List<TrainTest> trainTests();
	
	/**
	 * 完成培训
	 * @param brokerid
	 * @return
	 */
	@Update ( value = "UPDATE hs_user u SET u.istrained = 1 WHERE u.id=#{brokerid}")
	public abstract int trained(String brokerid);
	
	/**
	 * 完成测试
	 * @param brokerid
	 * @return
	 */
	@Update ( value = "UPDATE hs_user u SET u.istested = 1 WHERE u.id=#{brokerid}")
	public abstract int tested(String brokerid);
	/**
	 * 修改认证标志
	 * @param user
	 * @return
	 */
	@Update(value="UPDATE hs_user u set u.isauthen=1 WHERE u.id=#{userId}")
	public abstract int submit_authen(User user);
	/**
	 * 
	 * @param user
	 * @return
	 */
	@Insert(value="INSERT INTO hs_broker(positive_id_path,negative_id_path,hand_id_path,authen_tel,realname,id_num,status,user_id,create_date,tag)"
			+ " VALUES(#{positiveIdPath},#{negativeIdPath},#{handIdPath},#{authenTelephone},#{realName},#{idNum},1,"
			+ "#{userId},#{createDate},#{tag})")
	public abstract int save_broker_info(User user);
	
	@Select(value ="select * from hs_user_wallet where user_id=#{userId}")
	public abstract UserWalletBean getUserWalletBeanByUserId(String userId);
	
	@Select(value ="select * from hs_user_wallet where user_id=#{userId} and status=1")
	public abstract UserWalletBean getUsefullUserWalletBeanByUserId(String userId);
	
	@Insert(value ="insert into hs_user_wallet(user_id,status,recharge_amount,purchase_amount,tip_amount,frozen_amount,create_time,pay_password) "
			+ " values(#{user_id},#{status},#{recharge_amount},#{purchase_amount},#{tip_amount},#{frozen_amount},#{create_time},#{pay_password})")
	public abstract int addUserWallet(UserWalletBean wallet);
	
	@Update(value="update hs_user_wallet set pay_password=#{pay_password} where user_id=#{user_id}")
	public abstract int updateWalletPassword(UserWalletBean wallet);
	
	@Insert(value="insert into hs_user_withdraw_bind_account(id,user_id,account_type,account_no,real_name,bind_time) "
			+ "values(#{id},#{user_id},#{account_type},#{account_no},#{real_name},#{bind_time})")
	public abstract int addAccountBind(AccountBindBean bean);
	
	@Update(value="update hs_user_withdraw_bind_account set account_type=#{account_type},account_no=#{account_no},real_name=#{real_name} "
			+ " where id=#{id}")
	public abstract int updateAccountBind(AccountBindBean bean);
	
	@Select(value="select * from hs_user_withdraw_bind_account where id=#{id}")
	public abstract AccountBindBean getAccountBind(String id);
	
	@Select(value="select * from hs_user_withdraw_bind_account where user_id=#{user_id}")
	public abstract List<AccountBindBean> getAccountBinds(String user_id);
	
	@Update(value="update hs_user set username=#{username},gender=#{gender},birthday=#{birthday},header=#{header} where id=#{userId}")
	public abstract int updateUser(UserModel user);
	
	@Update(value="update hs_user set declaration=#{declaration} where id=#{user_id}")
	public abstract int declare(Map<String,Object> params);
	
	@Update(value="update hs_user_wallet set recharge_amount=recharge_amount+#{recharge_amount} where user_id=#{user_id}")
	public abstract int updateBalance(Map<String,Object> params);
	
	@Update(value="update hs_user_wallet set purchase_amount=purchase_amount+#{purchase_amount},tip_amount=tip_amount+#{tip_amount} where user_id=#{user_id}")
	public abstract int updateBrokerBalance(Map<String,Object> params);
	
	@Insert(value="insert into hs_user_wallet_log(id,user_id,trade_type,trade_desc,trade_time,amount,purchase_amount,tip_amount,order_id,order_no,service_amount) "
			+ " values(#{id},#{user_id},#{trade_type},#{trade_desc},#{trade_time},#{amount},#{purchase_amount},#{tip_amount},#{order_id},#{order_no},#{service_amount})")
	public abstract int addWalletLog(UserWalletLogBean walletLog);
	
	
	@Insert(value="insert into hs_pay_order(id,pay_type,pay_way,amount,pay_order_no,user_id,status,order_id,coupon_type,coupon_id,create_time) "
			+ " values(#{id},#{pay_type},#{pay_way},#{amount},#{pay_order_no},#{user_id},#{status},#{order_id},#{coupon_type},#{coupon_id},#{create_time}) ")
	int addPayOrder(PayOrderBean pay);
	
	@Select(value="select *,id as payid from hs_pay_order where id=#{id}")
	PayOrder getPayOrderById(String id);
	
	@Select(value="select *,id as payid from hs_pay_order where pay_order_no=#{pay_order_no}")
	PayOrder getPayOrderByPayOrderNo(String pay_order_no);
	
	@Update(value="update hs_pay_order set status=1  where pay_order_no=#{pay_order_no}")
	int payed(String pay_order_no);
	
	@Select(value="select *,id as rechargeid from hs_recharge_list where id=#{id}")
	RechargeList getRechargeList(String id);
	
	@Select(value="select *,id as rechargeid from hs_recharge_list")
	List<RechargeList> getRechargeLists();
	
	@Insert(value="insert into hs_withdraw_order(id,withdraw_order_no,user_id,withdraw_type,withdraw_account_no,create_time,withdraw_amount,status,withdraw_desc,real_name,service_amount) "
			+ " values(#{id},#{withdraw_order_no},#{user_id},#{withdraw_type},#{withdraw_account_no},#{create_time},#{withdraw_amount},#{status},#{withdraw_desc},#{real_name},#{service_amount}) ")
	int addWithdrawOrder(WithdrawOrderBean withdraw);
	
	@Update(value="update hs_user_wallet set frozen_amount=frozen_amount+#{purchase_amount}+#{tip_amount},purchase_amount=purchase_amount-#{purchase_amount},tip_amount=tip_amount-#{tip_amount} where user_id=#{user_id}")
	public abstract int frozenBrokerBalance(Map<String,Object> params);
	
	@SelectProvider(method="getBills",type=UserProvider.class)
	List<WalletBean> getBills(Map<String,Object> params);
	
	@SelectProvider(method="getBillsByType",type=UserProvider.class)
	List<BillBean> getBillsByType(Map<String,Object> params);
	
	@Insert(value="insert into hs_feedback(text,create_date,user_id,version,system) values(#{text},#{create_date},#{user_id},#{version},#{system})")
	int addFeedback(Feedback feedback);
	
	@Select(value="select sum(o.fee) from hs_order o where o.broker_id=#{user_id} and o.order_status_id>7")
	BigDecimal getPurchaseSumAmount(String user_id);
	
	@Select(value="select sum(o.tip) from hs_order o where o.broker_id=#{user_id} and o.order_status_id>7 ")
	BigDecimal getTipSumAmount(String user_id);
	
	@Select(value="select sum(o.withdraw_amount) from hs_withdraw_order o where o.user_id=#{user_id}")
	BigDecimal getWithdrawSumAmount(String user_id);
	
	 @Select("select count(1) from hs_pay_order l where l.user_id = #{userId} and l.pay_way=1 and l.status=1")
	 Integer queryRechargeWalletLogCount(String userId);
	 
	 @Insert(value="insert into hs_invite(user_id,invite_user_id,create_time,status)"
	 		+ " values(#{user_id},#{invite_user_id},#{create_time},#{status})")
	 int addInvite(Invite invite);
	 
	 @Select(value="select * from hs_invite i where i.status=1 and i.user_id=#{user_id}")
	 Invite getNotPrizeInvite(String user_id);
	 
	 @Update(value="update hs_broker set tag=#{tag} where user_id=#{user_id}")
	 int updateUserTag(Map<String, Object> params);
	 
	 @Update(value="update hs_user set point=point+#{point} where id=#{user_id}")
	 int updatePoint(Map<String,Object> params);
	 
	 @Select(value="select * from hs_institution order by id asc")
	 List<Institution> getInstitutions();
	 
	 @Select("select *,id as withdrawid from hs_withdraw_order where create_time <= #{time} and batch_order_no is null and status =1 order by create_time limit #{size}")
	 public abstract List<WithdrawOrder> queryWithdrawOrders(Map<String,Object> param);
	 
	 @Select("select count(1) from hs_withdraw_order where create_time <= #{time} and batch_order_no is null and status =1 ")
	 public abstract int queryWithdrawOrderCount(String time);
	 
	 @UpdateProvider(type = UserProvider.class, method = "batchUpdateWithdrawOrder")
	 public abstract void batchUpdateWithdrawOrder(Map<String,Object> param);
	 
	 @UpdateProvider(type = UserProvider.class, method = "batchUpdateAliWithdrawOrder")
	 public abstract void batchUpdateAliWithdrawOrder(Map<String,Object> param);
	 
	 @Update("update hs_batch_withdraw_order o set o.status = #{status} ,o.finish_time = #{finishTime} ,o.notify_id = #{notifyId} where id = #{batchOrderId}")
	 public abstract void updateBatchWithdrawOrder(Map<String, Object> param);
	 
	 @SelectProvider(type = UserProvider.class, method = "queryBrokerWalletLogsByOrderNo")
	 public abstract List<UserWalletLogBean> queryBrokerWalletLogsByOrderNo(Map<String,Object> queryMap);
	 
	 @UpdateProvider(type = UserProvider.class, method = "batchInsertBrokerWalletLog")
	 public abstract void batchInsertBrokerWalletLog(Map<String,Object> param);
	 
	 @UpdateProvider(type = UserProvider.class, method = "batchUpdateBrokerWallet")
	 public abstract void batchUpdateBrokerWallet(Map<String,Object> param);
	 
	 @Insert("insert into batch_withdraw_order(id,batch_type,batch_order_no,batch_fee,batch_num,create_time,status,latest_time) "
	 		+ " values (#{id},#{batch_type},#{batch_order_no},#{batch_fee},#{batch_num},#{create_time},0,#{latest_time})")
	 public abstract void insertBatchWithdrawOrder(BatchWithdrawOrder batchWithdrawOrder);
	 
	 @Select("select *,id as withdrawid from hs_withdraw_order o where o.batch_order_no = #{batchOrderNo} and o.status=1 and withdraw_type =1")
	 public abstract List<WithdrawOrder> queryAliWithdrawOrderByBatchOrderNo(String batchOrderNo);
	 
	 @Select("select * from hs_batch_withdraw_order o where o.batch_order_no = #{batchOrderNo} ")
	 public abstract BatchWithdrawOrder queryBatchWithdrawOrderByOrderNo(String batchOrderNo);
	 
	 @UpdateProvider(type = UserProvider.class, method = "batchDeductBrokerWallet")
	 public abstract void batchDeductBrokerWallet(Map<String, Object> param);
	
}
